rm(list=ls())
library(tidyverse)
library(readxl)

# Read average earnings for census 2000,2010
file_avg <- list.files(here::here("data","raw","IBGE"), pattern = "tab2033_avg_earnings", full.names = T)

avg_earnings <- read_excel(file_avg,
                           sheet = "Tabela",
                           skip = 5,
                           col_names = c("mun_code","mun_name","avg_inc_2000","avg_inc_2010"),
                         na = "...") %>% 
  filter(!str_detect(mun_code,"Fonte")) %>% 
  mutate(mun_code = substring(mun_code,1,6)) 

avg_earnings_long <- avg_earnings %>% 
  pivot_longer(cols = avg_inc_2000:avg_inc_2010,names_to = "year", values_to = "avg_earnings") %>% 
  mutate(year = as.numeric(str_extract(year,"[0-9]{4}"))) %>% 
  arrange(mun_code,year)

# Read median earnings for census 2000,2010
file_median <- list.files(here::here("data","raw","IBGE"), pattern = "tab2033_median_earnings", full.names = T)

median_earnings <- read_excel(file_median,
                           sheet = "Tabela",
                           skip = 5,
                           col_names = c("mun_code","mun_name","median_inc_2000","median_inc_2010"),
                           na = "...") %>% 
  filter(!str_detect(mun_code,"Fonte")) %>% 
  mutate(mun_code = substring(mun_code,1,6)) 

median_earnings_long <- median_earnings %>% 
  pivot_longer(cols = median_inc_2000:median_inc_2010,names_to = "year", values_to = "median_earnings") %>% 
  mutate(year = as.numeric(str_extract(year,"[0-9]{4}"))) %>% 
  arrange(mun_code,year)

# Join avg and median
earnings <- avg_earnings_long %>% 
  full_join(median_earnings_long, by = c("mun_code","year","mun_name"))

# Deflate data
gdp_deflator <- read_rds(here::here("data","processed","gdp_deflator.rds"))

earnings_deflated <- earnings %>% 
  left_join(gdp_deflator, by = c("year")) %>% 
  mutate(avg_earnings = avg_earnings/gdp_deflator,
         median_earnings = median_earnings/gdp_deflator) %>% 
  select(-gdp_deflator)

# SaveRdS ----

write_rds(earnings_deflated, here::here("data","processed","citycharacteristics","earnings_mun.rds"))
